﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Mono.Data.Sqlite;
using NLog;
//using System.Data.Sqlite;

namespace Tab2Sql.Classes.db
{
    /// <summary>
    ///
    /// </summary>
    public class SqliteDB
    {

		private static Logger logger = LogManager.GetCurrentClassLogger ();
		/// <summary>
		/// Constructor. If file name does not exist then nothing further will occur.
		/// </summary>
		/// <param name="fileName"></param>
		public SqliteDB (string fileName)
		{
			
			
			schema = new DataTable ();
			hasSchemaBeenGenerated = false;
			statements = new List<string> ();
			errorList = new List<string> ();
			myConn = new SqliteConnection ();
			
			connectionStringBuilder = new SqliteConnectionStringBuilder ();
			string currentPath = Environment.CurrentDirectory;
			string fullPath = System.IO.Path.Combine (currentPath, fileName);
			logger.Debug ("Sqlite database file is {0}. Full path is {1}", fileName,fullPath);
			connectionStringBuilder.DataSource = fullPath;
			connectionStringBuilder.FailIfMissing = false;
			myConn.ConnectionString = connectionStringBuilder.ConnectionString;
			try {
				var cmd = new SqliteCommand (myConn);
				myCommand = cmd;
			} catch (Exception ex) {
				logger.Error (ex.Message);
				SqliteConnection.CreateFile (fullPath);
				var cmd2 = new SqliteCommand (myConn);
				myCommand = cmd2;
			}
		}
		
		/// <summary>
        ///
        /// </summary>
        public string LastStatement
        {
            get
            {
                string tmp = "";
                int i = 0;
                foreach (var s in statements)
                {
                    if (i > 10)
                    {
                        break;
                    }
                    tmp += string.Format ("{0} - {1}\n", i, s.Trim ());
                    i++;
                }
                return tmp;
            }
        }
        /// <summary>
        ///
        /// </summary>
        public string LastError;

        private int lastRowCount;
        /// <summary>
        ///
        /// </summary>
        public int LastRowCount
        {
            get { return lastRowCount; }
            
        }
        internal List<string> errorList;
        internal List<string> statements;

        internal SqliteCommand myCommand;
        internal SqliteConnection myConn;
        internal StringBuilder Output;
        internal SqliteConnectionStringBuilder connectionStringBuilder;

        private bool hasSchemaBeenGenerated;

        private DataTable schema;
        /// <summary>
        ///
        /// </summary>
        public DataTable Schema
        {
            get
            {
                if (hasSchemaBeenGenerated == false)
                {
                    initSchema();
                }
                return schema;
            }
        }

        string connectionString;


        
        /// <summary>
        /// Tries to create a table within Sqlite DB of name supplied with the columns in columnNames array.
        /// All columns are created as text datatype. 
        /// Tests for existence of table before creating - will fail if so with status code of -2.
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnNames"></param>
        /// <returns></returns>
        public int CreateTable(string tableName, string[] columnNames)
        {
            int endResult=-1;
            string columnSql = produceColumnStatement(columnNames);
            string sql = string.Format("CREATE TABLE {0} ({1})", tableName, columnSql);
            if (myConn != null)
            {
                endResult = -2;
                if (!Exists(tableName))
                {
                    endResult = ExecuteSql(sql);
                }
            }
            return endResult;
        }
        /// <summary>
        /// Displays system table from Sqlite. ShowFull param controls whether you just get table names or full info.
        /// </summary>
        /// <param name="showFull"></param>
        /// <returns></returns>
        public string DisplaySchema(bool showFull)
        {
            return DGen.DText.ObjectToString.DataTableToString(Schema);
        }


/// <summary>
///
/// </summary>
        public string BasicInfo
        {
            get
            {
                return this.myConn.ConnectionString.ToString();
            }
        }
        /// <summary>
        ///
        /// </summary>
        public string DataSource
        {
            get
            {
                if (this.myConn.DataSource != null)
                {
                    return this.myConn.DataSource;
                }
                else
                {
                    return "UNKNOWN";
                }
            }
        }
        /// <summary>
        ///
        /// </summary>
        public string FileName
        {
            get
            {
                return System.IO.Path.Combine(Environment.CurrentDirectory, this.DataSource);
            }
        }


        /// <summary>
        /// Responses :
        ///     Greater than Zero - number of rows affected
        ///     Zero   - no rows affected
        ///     -1     - No connection object. 
        ///     -2     - Failed before command object fired
        ///     -3     - Failed at command object (unknown error)
        /// </summary>
        /// <param name="sqlStatement"></param>
        /// <returns></returns>
        public int ExecuteSql(string sqlStatement)
        {
            if (myConn != null)
            {
                int response = -2;
                myCommand = new SqliteCommand(sqlStatement,myConn);
                try
                {
                    statements.Add(sqlStatement);
                    tryOpen();

                    response = myCommand.ExecuteNonQuery();
                    lastRowCount = response;
                    tryClose();
                }
                catch
                {
                    
                    return -3;
                }
                return response;
                
            }
            return -1;
        }

       
        /// <summary>
        /// Does table exist. Returns True if it does.
        /// </summary>
        /// <param name="Table"></param>
        /// <returns></returns>
        public bool Exists(string Table)
        {
            tryOpen();
            DataTable myT = myConn.GetSchema(SqliteMetaDataCollectionNames.Tables);
            tryClose();
            foreach (DataRow dr in myT.Rows)
            {
                string tabName = dr["TABLE_NAME"].ToString();
                if (tabName.ToLower().Trim(new char[] { ' ', '"' }) == Table.ToLower().Trim(new char[] { ' ', '"' }))
                {
                    return true;
                }
            }
            return false;
        }
        /// <summary>
        /// Returns integer row count for table with supplied name. 
        /// Returns -2 = Failed to run command
        /// Returns -1 = Response is not an integer.
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int GetRowCount(string tableName)
        {
            int n = -2;
            bool r = Int32.TryParse(ExecuteScalar("SELECT COUNT(*) from " + tableName + ";"), out n);
            if (r == false) { return -1; }
            else { return n; }
        }
        /// <summary>
        /// Returns datatable for supplied command.
        /// </summary>
        /// <param name="sqlStatement"></param>
        /// <returns></returns>
        public DataTable RenderSql(string sqlStatement)
        {
            DataTable dt = new DataTable();
            
            if (myConn != null)
            {
                myCommand = new SqliteCommand(sqlStatement,myConn);
                statements.Add(sqlStatement);
                tryOpen();

                var d = myCommand.ExecuteReader(CommandBehavior.SingleResult);
                dt.BeginLoadData();
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                ds.EnforceConstraints = false;
                
                
                if (d.HasRows)
                {
                    ds.Load(d, LoadOption.Upsert, dt);

                }
                if (ds.HasErrors)
                {
                    DataRow[] dr2 = dt.GetErrors();
                    foreach (var dr3 in dr2)
                    {
                        string a = dr3[0].ToString();

                    }
                }
                tryClose();
                lastRowCount = d.RecordsAffected;
                dt.AcceptChanges();

            }
            return dt;
        }
        /// <summary>
        /// Returns text formatted output of resultset.
        /// </summary>
        /// <param name="sqlStatement"></param>
        /// <returns></returns>
        public string RenderAsTextSql(string sqlStatement)
        {
            DataTable dt = RenderSql(sqlStatement);
            if (dt.Rows.Count > 0)
            {
                return DGen.DText.ObjectToString.DataTableToString(dt);
            }
            else { return "No Rows"; }
        }

        /// <summary>
        /// Returns single column as List for given statement. 
        /// Sql query does not have to limit response to one column - additional values are ignored.
        /// </summary>
        /// <param name="sqlStatement"></param>
        /// <returns></returns>
        public List<string> GetSingleColumnSql(string sqlStatement)
        {
            List<string> endResult = new List<string>();
            DataTable dt = RenderSql(sqlStatement);
            if (dt.Columns.Count >= 1)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    endResult.Add(dr[0].ToString());
                }
            }
            return endResult;
        }

        
        /// <summary>
        /// Returns first col-first row of resultset. 
        /// </summary>
        /// <param name="sqlStatement"></param>
        /// <returns></returns>
        public string ExecuteScalar(string sqlStatement)
        {
            DataTable dt = new DataTable();
            if (myConn != null)
            {
                statements.Add(sqlStatement);
                tryOpen();
                myCommand = new SqliteCommand(sqlStatement, myConn);

        
                var d = myCommand.ExecuteScalar();
                if (d != null)
                {
                    if (d.ToString().Length == 0) { lastRowCount = 0; }
                    else { lastRowCount = 1; }
                }
                else { lastRowCount = 0; d = lastRowCount.ToString(); }
                tryClose();
                return d.ToString();
                
            }
            return "No connection object.";
        }


        private void dropIfExists(string theTableName)
        {
            var myCmd = new SqliteCommand(myConn);
            if (Exists(theTableName))
            {
                tryOpen();
                myCmd.CommandText = "DROP TABLE " + theTableName;
                myCmd.ExecuteNonQuery();
                tryClose();
            }
        }
        private string produceColumnStatement(string[] columnNames)
        {
            string endString = String.Empty;
            foreach (var item in columnNames)
            {
                endString += string.Format("{0} TEXT,", item);
            }
            return endString;
        }

        private void tryOpen()
        {
            
            if (myConn == null)
            {
                try
                {
                    myConn = new SqliteConnection();
                }
                catch (Exception ex)
                {
                    errorList.Add(ex.Message);
                }
            }
            if (myConn != null)
            {
                if (myConn.State != ConnectionState.Open)
                {
                    try
                    {
                        myConn.Open();
                    }
                    catch { }
                }
            }

        }

        private void tryClose()
        {
            if (myConn.State == ConnectionState.Open || myConn.State == ConnectionState.Broken)
            {
                myConn.Close();
            }
        }

        private void initSchema()
        {
            if (hasSchemaBeenGenerated == false)
            {
                tryOpen();
                DataTable tmpSchema = myConn.GetSchema(SqliteMetaDataCollectionNames.Tables);
                schema = new DataTable();
                schema.Columns.Add("Name");
                foreach (DataRow dr in tmpSchema.Rows)
                {
                    DataRow nr = schema.NewRow();
                    nr[0] = dr["TABLE_NAME"];
                    schema.Rows.Add(nr);
                }
                schema.AcceptChanges();
                tryClose();
                hasSchemaBeenGenerated = true;
            }
        }

    }
}
